package org.wesc.boot.common.poi;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel读写工具类
 * @author Wesley
 */
@Slf4j
public class ExcelReader {
    private final static String SUFFIX_XLS = "xls";
    private final static String SUFFIX_XLSX = "xlsx";

    /**
     * 读入excel文件，解析后返回
     *
     * @param file 上传的文件
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<>();
        if (workbook != null) {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();

                //获取开始行的列数，以此为标准
                Row firstRow = sheet.getRow(firstRowNum);
                int rowStartIndex = firstRow.getFirstCellNum();
                int rowEndIndex = firstRow.getPhysicalNumberOfCells();
                int rowSize = firstRow.getPhysicalNumberOfCells();
                log.info("excel row from " + rowStartIndex + " to " + rowEndIndex);
                log.info("rowSize:" + rowSize);

                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环所有行
                for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    String[] cells = new String[rowSize];
                    //循环当前行
                    for (int cellNum = rowStartIndex; cellNum < rowEndIndex; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        try {
                            cells[cellNum] = getCellValue(cell);
                        } catch (Exception e) {
                            log.error("row_number:" + rowNum + ", cell_number:" + cellNum);
                        }

                    }
                    list.add(cells);
                }
            }
            workbook.close();
        }
        return list;
    }

    /**
     * 上传文件检查
     */
    public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            log.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        if (null == fileName) {
            return;
        }
        //判断文件是否是excel文件
        if (!fileName.endsWith(SUFFIX_XLS) && !fileName.endsWith(SUFFIX_XLSX)) {
            log.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 依据上传excel获取workbook
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        if (null == fileName) {
            return null;
        }
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(SUFFIX_XLS)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(SUFFIX_XLSX)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.info(e.getMessage());
        }
        return workbook;
    }

    /**
     * 格式化单元格数据
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                try {
                    cellValue = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BLANK:
            case ERROR:
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

}
